const mysql=require("mysql2");
const dbconfig=require("./dbconfig")

//创建连接池
const pool = mysql.createPool({
    ...dbconfig
}); 
// 执行语句
function execute(sql, data = []) {
    let connPromise = pool.promise();
    return connPromise.execute(sql, data);
  }

const select = async (tablename,fields='*')=>{
    fields = fields == '*' ? fields : fields.join(',');
    let sql = `select ${fields} from ${tablename}`;
    let result = await execute(sql, []);
  return result[0];
}

// 新增
async function insert(tablename, statement) {
  let statementkey = Object.keys(statement);
  let val_temp = '?,'.repeat(statementkey.length - 1) + '?';
  let sql = `insert into ${tablename} (${statementkey.join(',')}) values(${val_temp})`;
  console.log(sql)
  return await execute(sql, Object.values(statement));
}
// 多表查询
async function selectmoretab(sql) {
  return await execute(sql);
}

// 修改
async function update(tablename, { updateFields = {}, where_and = [], where_or = [] }) {
  let where_and_ = where_and.join(' and ');
  let where_or_ = where_or.join(' or ');
  let where_ = where_and_ + where_or_;
  where_ = where_ == '' ? '' : ' where ' + where_;
  let keyValues = [];
  for (let key in updateFields) {
      keyValues.push(`${key}=${updateFields[key]}`);
  }
  let setValues = keyValues.join(', ');
  let sql = `update ${tablename} set ${setValues} ${where_}`;
  console.log(sql);
  return (await execute(sql, []))[0];
}

module.exports={
    select,
    insert,
    selectmoretab,
    update
}